import pandas as pd
from openpyxl.styles import numbers
from openpyxl.chart import Series,LineChart, Reference
from openpyxl.styles import Font,Border,Alignment,Side,PatternFill
from openpyxl.worksheet.hyperlink import Hyperlink
from openpyxl import load_workbook

font = Font(size=11, bold=False, name='微软雅黑',  color="000000")
alignment_center = Alignment(horizontal='center', vertical='center')
gray_fill = PatternFill('solid', fgColor='D3D3D3')  # 浅灰色填充
thin = Side(border_style="thin", color="000000")#边框样式，颜色
border = Border(left=thin, right=thin, top=thin, bottom=thin)#边框的位置
thousands_format = '#,##0' #千位分隔符且不保留小数
percentage_format = numbers.FORMAT_PERCENTAGE #百分比


def adjust_format(file_path):
    # 打开Excel文件
    wb = load_workbook(file_path)

    # 遍历每个工作表并应用样式
    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        # 设置行高为18
        ws.row_dimensions[1].height = 18

        # 设置列宽自适应
        for col in ws.columns:
            max_length = 0
            column = col[0].column_letter
            for cell in col:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(cell.value)
                except:
                    pass
            adjusted_width = (max_length + 2) * 2
            ws.column_dimensions[column].width = adjusted_width

        for row_num, row in enumerate(ws.iter_rows(min_row=2, min_col=2, max_row=ws.max_row, max_col=ws.max_column)):
            has_value = False
            for cell in row:
                if cell.value:
                    has_value = True
                    break
        
            for cell in row:
                cell.font = font
                cell.alignment = alignment_center
                if row_num == 0 and has_value:
                    cell.fill = gray_fill  # 第一行且有值为灰色填充
                cell.border = border
        
        # 数值列格式
        for col in ['D', 'E', 'F']:
            for cell in ws[col]:
                cell.number_format = thousands_format
        # 占比列格式
        for cell in ws['G']:
            if filter == '已提交审核':
                cell.number_format = '0.00%' 
            else: cell.number_format = percentage_format
        
    # 保存Excel文件
    wb.save(file_path) 
